/**
 * 
 */
package dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import model.HOC_KY_KHHTM;
import model.HocKy_KHHT_CoMonHocM;
import model.LoaiNhomMonHocM;
import model.MonHocM;
import util.DAL;
import util.MyConnectUnit;

public class HocKy_KHHT_CoMonHocD {
	private MyConnectUnit conn;

	/**
	 * @throws Exception 
	 * @function constructor HocKy_KHHT_CoMonHocD
	 */
	public HocKy_KHHT_CoMonHocD() throws Exception {
		super();
		this.conn = DAL.getDAL();
	}
	/**
	 * @function get all HOC_KY_KHHT_CO_MON_HOC
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HocKy_KHHT_CoMonHocM> getAlls(String ten, int from, int to)throws Exception {
		String limit = from + ", " + to;
		ArrayList<HocKy_KHHT_CoMonHocM> ls = new ArrayList<HocKy_KHHT_CoMonHocM>();
		ResultSet rs = this.conn.Selects(
						"h.id_hoc_ky_khht, hk.ten, h.id_mon_hoc, m.ma, m.ten, m.so_tc ,h.id_loai_nhom_mon, l.ten",
						"HOC_KY_KHHT_CO_MON_HOC h, MON_HOC m, LOAI_NHOM_MON_HOC l, HOC_KY_KHHT hk",
						"h.id_mon_hoc = m.id_mon_hoc AND h.id_loai_nhom_mon = l.id_loai_nhom_mon_hoc AND h.id_hoc_ky_khht = hk.id_hoc_ky_khht AND hk.ten LIKE '%"
								+ ten + "%'", "hk.ten ASC", limit);
		HocKy_KHHT_CoMonHocM l = null;
		while (rs.next()) {
			l = new HocKy_KHHT_CoMonHocM();
			
			l.setId_hoc_ky_khht(rs.getInt("h.id_hoc_ky_khht"));
			HOC_KY_KHHTM hk = new HOC_KY_KHHTM();
			hk.setTen(rs.getString("hk.ten"));
			l.setTen_hoc_ky_khht(hk);
			
			l.setId_mon_hoc(rs.getInt("h.id_mon_hoc"));
			MonHocM m = new MonHocM();
			m.setMa(rs.getString("m.ma"));
			m.setTen(rs.getString("m.ten"));
			m.setSo_tc(rs.getInt("m.so_tc"));
			l.setMa_mon_hoc(m);
			l.setTen_mon_hoc(m);
			l.setSo_tc(m);
					
			l.setId_loai_nhom_mon(rs.getInt("h.id_loai_nhom_mon"));
			LoaiNhomMonHocM ln = new LoaiNhomMonHocM();
			ln.setTen(rs.getString("l.ten"));
			l.setTen_loai_nhom_mon(ln);
			
			ls.add(l);
		}
		return ls;
	}
	
	
	/**
	 * @function getDetail HOC_KY_KHHT_CO_MON_HOC by id
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HocKy_KHHT_CoMonHocM> getDetail(int id)throws Exception {
		ArrayList<HocKy_KHHT_CoMonHocM> ls = new ArrayList<HocKy_KHHT_CoMonHocM>();
		ResultSet rs = this.conn.Selects(
						"h.id_hoc_ky_khht, h.id_mon_hoc, m.ma, m.ten, m.so_tc ,h.id_loai_nhom_mon, l.ten",
						"HOC_KY_KHHT_CO_MON_HOC h, MON_HOC m, LOAI_NHOM_MON_HOC l",
						"h.id_mon_hoc = m.id_mon_hoc AND h.id_loai_nhom_mon = l.id_loai_nhom_mon_hoc AND h.id_hoc_ky_khht = " + id, "l.ten ASC", null);
		HocKy_KHHT_CoMonHocM l = null;
		while (rs.next()) {
			l = new HocKy_KHHT_CoMonHocM();
			l.setId_hoc_ky_khht(rs.getInt("h.id_hoc_ky_khht"));
			l.setId_mon_hoc(rs.getInt("h.id_mon_hoc"));
			MonHocM m = new MonHocM();
			m.setMa(rs.getString("m.ma"));
			m.setTen(rs.getString("m.ten"));
			m.setSo_tc(rs.getInt("m.so_tc"));
			l.setMa_mon_hoc(m);
			l.setTen_mon_hoc(m);
			l.setSo_tc(m);
					
			l.setId_loai_nhom_mon(rs.getInt("h.id_loai_nhom_mon"));
			LoaiNhomMonHocM ln = new LoaiNhomMonHocM();
			ln.setTen(rs.getString("l.ten"));
			l.setTen_loai_nhom_mon(ln);
			
			ls.add(l);
		}
		return ls;
	}


	/**
	 * @function get all HocKy_KHHT_CoMonHocM
	 * @param Condition
	 * @param Limit
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HocKy_KHHT_CoMonHocM> getAll(String Condition, String Limit, String OrderBy) throws Exception {
		ArrayList<HocKy_KHHT_CoMonHocM> ls = new ArrayList<HocKy_KHHT_CoMonHocM>();
		ResultSet rs = this.conn.Select("HOC_KY_KHHT_CO_MON_HOC", Condition, Limit, OrderBy);
		HocKy_KHHT_CoMonHocM l = null;
		while (rs.next()) {
			l = new HocKy_KHHT_CoMonHocM();
			l.setId_hoc_ky_khht(rs.getInt("id_hoc_ky_khht"));
			l.setId_mon_hoc(rs.getInt("id_mon_hoc"));
			l.setId_loai_nhom_mon(rs.getInt("id_loai_nhom_mon"));
			ls.add(l);
		}
		return ls;
	}

	/**
	 * @function get all HocKy_KHHT_CoMonHocM with Condition + Limit
	 * @param Condition
	 * @param Limit
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HocKy_KHHT_CoMonHocM> getAll(String Condition, String Limit) throws Exception {
		return getAll(Condition, Limit, null);
	}

	/**
	 * @function get all HocKy_KHHT_CoMonHocM with Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HocKy_KHHT_CoMonHocM> getAll(String Condition) throws Exception {
		return getAll(Condition, null);
	}

	/**
	 * @function get all MonHoc no parameter
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HocKy_KHHT_CoMonHocM> getAll() throws Exception {
		return getAll(null);
	}
	
	/**
	 * @function check exists HocKy_KHHT_CoMonHocM
	 * @param id_mon_hoc
	 * @param id_hoc_ky_khht
	 * @return
	 * @throws Exception
	 */
	public int checkExists(int id_mon_hoc, int id_hoc_ky_khht) throws Exception {
		ArrayList<HocKy_KHHT_CoMonHocM> list = getAll("id_mon_hoc = " + id_mon_hoc + " AND id_hoc_ky_khht = " + id_hoc_ky_khht);
		return list.size();
	}

	/**
	 * @function insert HOC_KY_KHHT_CO_MON_HOC
	 * @param hk
	 * @return
	 * @throws Exception
	 */
	public boolean Insert(HocKy_KHHT_CoMonHocM hk) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("id_hoc_ky_khht",hk.getId_hoc_ky_khht());
		map.put("id_mon_hoc",hk.getId_mon_hoc());
		map.put("id_loai_nhom_mon",hk.getId_loai_nhom_mon());
		return this.conn.Insert("HOC_KY_KHHT_CO_MON_HOC", map);
	}

	/**
	 * @function update HOC_KY_KHHT_CO_MON_HOC
	 * @param hk
	 * @return
	 * @throws Exception
	 */
	public boolean Update(HocKy_KHHT_CoMonHocM hk) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("id_loai_nhom_mon",hk.getId_loai_nhom_mon());
		return this.conn.Update("HOC_KY_KHHT_CO_MON_HOC", map, "id_mon_hoc = " + hk.getId_mon_hoc() + " AND id_hoc_ky_khht = " + hk.getId_hoc_ky_khht());
	}

	/**
	 * @function delete HOC_KY_KHHT_CO_MON_HOC
	 * @param id_mon_hoc
	 * @param id_hoc_ky_khht
	 * @return
	 * @throws Exception
	 */
	public boolean Delete(int id_mon_hoc, int id_hoc_ky_khht) throws Exception {
		return this.conn.Delete("HOC_KY_KHHT_CO_MON_HOC", "id_mon_hoc = " + id_mon_hoc + " AND 	id_hoc_ky_khht = " + id_hoc_ky_khht);
	}
}
